Question & Answer
Question
How do you end a large amount of Jobs with similar names quickly.
Cause
There might be a time where a run away job starts thousands of job in the system. This document creates an automated way of ending them using SQL.
Answer
With the introduction of the IBM i SQL Service ACTIVE_JOB_INFO table function, you can create an SQL report containing a list of active similar jobs on the system. Once the list is created, we can use the results to end the jobs.
Authorization: None required to see general information or information about your own jobs.
For DETAILED_INFO => ALL:
:
The SQL ends all of the jobs in the report. No Joblogs are generated.
- All users can see detailed column information for CLIENT_IP_ADDRESS, PAGE_FAULTS, JOB_ACTIVE_TIME, PRESTART_JOB_REUSE_COUNT, and PRESTART_JOB_MAX_USE_COUNT.
- A user with QIBM_DB_SQLADM or QIBM_DB_SYSMON function usage authority can see detailed column information that relates to SQL activity starting with the SQL_STATEMENT_TEXT column through the PSEUDO_CLOSED_CURSOR_COUNT column.
- For a user with *JOBCTL user special authority, all detailed column information is returned.
To get a listing of all active jobs with similar names follow the steps:
1) Open the ACS Run SQL Scripts tool
2) Run the following SQL:
SELECT 'CL: ENDJOB JOB(' || JOB_NAME || ') OPTION(*IMMED) LOGLMT(0);'
FROM TABLE ( QSYS2.ACTIVE_JOB_INFO() ) AS X
WHERE JOB_NAME LIKE '%TESTV%';
Note: The report displays a list of jobs that have 'TESTV' as part of the job name. Change the SQL to match the name of the jobs in your system.
Note 2: The SQL creates the command string to end the jobs.
Right Click on the Results Pane and use the option to Select ALL:
Right Click and select Copy and Paste the result as a new SQL Statement.
In my sample there was only one job, but you may receive pages of these.
Run the SQL:
:The SQL ends all of the jobs in the report. No Joblogs are generated.
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000000CHAAA2","label":"Operating System"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"7.2;7.3;7.4","Line of Business":{"code":"LOB57","label":"Power"}}]
Was this topic helpful?
Document Information
Modified date:
29 December 2021
UID
ibm16244954